package com.user.daoImpl;

import com.user.dao.UserDAO;
import com.user.util.MysqlConnectUtil;
import com.user.vo.User;

import java.sql.*;
import java.util.*;

public class UserDaoImpl implements UserDAO {

    Connection conn = null;
    PreparedStatement ps = null;

    ResultSet rs = null;

    /**
     * 使用静态块加载数据库驱动
     */
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int addUser(User user){

        int insertCount = 0;

        try {
            //获取连接
            conn = MysqlConnectUtil.getConn();

            //写添加用户的sql语句
            String sql = "insert into user values(?,?,?,?,?)";

            //把sql语句进行预处理
            ps = conn.prepareStatement(sql);

            //设置参数
            ps.setString(1,user.getId());
            ps.setString(2,user.getName());
            ps.setInt(3,user.getAge());
            ps.setString(4,user.getUsername());
            ps.setString(5,user.getPassword());

            //执行语句
            insertCount = ps.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            MysqlConnectUtil.closed(conn,ps);
        }

        return insertCount;
    }

    @Override
    public boolean deleteUser(String userId) {

        boolean flag = true;

        try {
            conn = MysqlConnectUtil.getConn();

            String sql = "delete from user where id=?";

            ps = conn.prepareStatement(sql);
            ps.setString(1,userId);

            int delCount = ps.executeUpdate();

            if(delCount != 1){
                flag = false;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            MysqlConnectUtil.closed(conn,ps);
        }

        return flag;
    }

    @Override
    public void updateUser(User user) {

        try {
            conn = MysqlConnectUtil.getConn();

            String sql = "update user set name=?,age=?,username=?,password=? where id=?";

            ps = conn.prepareStatement(sql);

            ps.setString(1,user.getName());
            ps.setInt(2,user.getAge());
            ps.setString(3,user.getUsername());
            ps.setString(4,user.getPassword());
            ps.setString(5,user.getId());

            int updCount = ps.executeUpdate();

            if(updCount==1){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            MysqlConnectUtil.closed(conn,ps);
        }

    }

    @Override
    public User selectUser(String username, String password) {

        User user = null;

        try{
            conn = MysqlConnectUtil.getConn();

            String sql = "select * from user where username=? and password=?";

            ps = conn.prepareStatement(sql);

            ps.setString(1,username);
            ps.setString(2,password);

            rs = ps.executeQuery();

            //如果能确定返回的结果一定小于等于1 可以使用if 否则使用while
            if(rs.next()){
                user = new User();
                user.setId(rs.getString("id"));
                user.setName(rs.getString("name"));
                user.setAge(rs.getInt("age"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            MysqlConnectUtil.closed(conn,ps,rs);
        }

        return user;
    }

    @Override
    public List<User> selectAllUser() {

        List<User> userList = new ArrayList<>();

        try{
            conn = MysqlConnectUtil.getConn();

            String sql = "select * from user";

            ps = conn.prepareStatement(sql);

            rs = ps.executeQuery();

            //如果能确定返回的结果一定小于等于1 可以使用if 否则使用while
            while(rs.next()){
                User user = new User();
                user.setId(rs.getString("id"));
                user.setName(rs.getString("name"));
                user.setAge(rs.getInt("age"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                userList.add(user);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            MysqlConnectUtil.closed(conn,ps,rs);
        }

        return userList;
    }

    @Override
    public List<Map<String, Object>> selectMoreUser() {

        // Map中的多个键值对表示一行数据，每个键值对是这行数据的一列 比如 id=1001
        // key表示的是每列的列名所以一定是String,value表示的是这列的数据，不同的列的数据可能是不同的类型，所以用Object
        List<Map<String,Object>> userList = new ArrayList<>();

        try{

            conn = MysqlConnectUtil.getConn();

            String sql = "select ename,e.deptno,dname from emps as e " +
                    " left join dept as d " +
                    " on e.deptno=d.deptno ";

            ps = conn.prepareStatement(sql);

            rs = ps.executeQuery();

            //获取结果集的元数据信息
            ResultSetMetaData metaData = rs.getMetaData();

            //查询到的每条数据有多少列
            int columnCount = metaData.getColumnCount();

            //如果能确定返回的结果一定小于等于1 可以使用if 否则使用while
            while(rs.next()){
                //每条数据都是一个Map，所以每循环一次都需要创建一个新的Map
                Map user = new HashMap();

                //每条数据都有 columnCount 列，进行遍历封装Map
                for (int i = 1; i <= columnCount; i++) {
                    //通过列的序号获取列名
                    String columnName = metaData.getColumnName(i);
                    //通过列名或需要获取相应列的值
                    Object columnValue = rs.getObject(columnName);

                    user.put(columnName,columnValue);
                }
                //把封装好的 Map 添加到列表中
                userList.add(user);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            MysqlConnectUtil.closed(conn,ps,rs);
        }

        return userList;
    }
}
